SQL Server 2016 Developer's Guide by Dejan Sarka & Milos Radivojevic & William Durkin

Author:Dejan Sarka & Milos Radivojevic & William Durkin [Sarka, Dejan]
Language: eng
Format: azw3, epub
Publisher: Packt Publishing
Published: 2017-03-22T04:00:00+00:00

Note

You should specify permissions on the higher levels of the object hierarchy, namely on the schema level, and then handle exceptions. If you need column-level permissions, you should use programmable objects such as views and stored procedures. You should keep permissions as simple as possible.

The GRANT statement includes the WITH GRANT OPTION. This option indicates that the principal to whom you grant permission on an object can grant this permission on the same object to other principals.

The DENY statement comes with the CASCADE option. When you use this option with the DENY statement, you indicate that the permission you are denying is also denied to other principals to which it has been granted by this principal.

The REVOKE statement has the GRANT OPTION FOR and the CACSCADE options. GRANT OPTION FOR means you are revoking permission to grant the same permission to other principals (that is, you are revoking the WITH GRANT OPTION permission you gave to this principal by using the GRANT statement). The CASCADE option means you are revoking permission not just from the principal you mention in the statement but also from other principals to which permission has been granted by this principal. Note that such a cascading revocation revokes both the GRANT and DENY of that permission.

The following code shows how to use the object permissions. First, the code grants the CONTROL permission on dbo.Table1 to LoginB. LoginB can read the table.

GRANT CONTROL ON dbo.Table1 TO LoginB; GO EXECUTE AS USER = 'LoginB'; SELECT * FROM dbo.Table1; REVERT; GO

Next, you deny the SELECT permission on dbo.Table1 to LoginB. Note that LoginB still has the CONTROL permission on this table, so this user can insert into the table.

DENY SELECT ON dbo.Table1 TO LoginB; GO EXECUTE AS USER = 'LoginB'; INSERT INTO dbo.Table1(id, tableContainer) VALUES (2, 'dbo'); REVERT; GO

However, you denied the SELECT permission to LoginB. An explicit DENY for an ordinary user always supersedes all explicit GRANT. Therefore, the following code produces an error, stating that the SELECT permission is denied:

EXECUTE AS USER = 'LoginB'; SELECT * FROM dbo.Table1; REVERT; GO

Finally, security would not worth much if a user could change their own settings. The following code impersonates LoginB and tries to change the permissions to the same database user:

EXECUTE AS USER = 'LoginB'; REVOKE SELECT ON dbo.Table1 FROM LoginB; REVERT; GO

Of course, the previous code produced an error. However, you as the dbo database users, can change the permissions for the user LoginB, and therefore the following code succeeds:

REVOKE SELECT ON dbo.Table1 FROM LoginB; GO

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.